﻿using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Swashbuckle.AspNetCore.Annotations;
using System;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace CommonUtils
{
    public static class DbContextUtil
    {
        static Map<Type, object> Includes = new Map<Type, object>();

        public static void SetInclude<TEntity>(Expression<Func<TEntity, object>> include) where TEntity : DbEntity
        => Includes[typeof(TEntity)] = include;

        public static Expression<Func<TEntity, object>> GetInclude<TEntity>() where TEntity : DbEntity
        {
            var type = typeof(TEntity);
            if (!Includes.ContainsKey(type))
                return null;
            return Includes[type] as Expression<Func<TEntity, object>>;
        }

        public static IQueryable<TEntity> Queryable<TEntity>(this DbContext db) where TEntity : DbEntity
        {
            var include = GetInclude<TEntity>();
            if (include != null)
                return db.Set<TEntity>().Include(include).AsNoTracking();
            return db.Set<TEntity>().AsNoTracking();
        }

        public static IOrderedQueryable<TEntity> NewOrder<TEntity, TKey>(this DbContext db, Expression<Func<TEntity, TKey>> keySelector) where TEntity : DbEntity
        => db.Queryable<TEntity>().OrderBy(keySelector);

        public static IOrderedQueryable<TEntity> NewOrderDesc<TEntity, TKey>(this DbContext db, Expression<Func<TEntity, TKey>> keySelector) where TEntity : DbEntity
        => db.Queryable<TEntity>().OrderByDescending(keySelector);

        public static Page<TEntity> Page<TEntity>(this DbContext db, int? pageNum, int? pageSize, Expression<Func<TEntity, bool>> predicate, IOrderedQueryable<TEntity> order = null)
            where TEntity : DbEntity
        {
            var page = new Page<TEntity>(pageNum, pageSize);
            page.Amount = db.Queryable<TEntity>().Where(predicate).Count();
            if (page.Amount > 0 && page.Size > 0)
            {
                if (order != null)
                    page.Records = order.Where(predicate).Skip(page.Skip).Take(page.Size).ToArray();
                else
                    page.Records = db.Queryable<TEntity>().Where(predicate).Skip(page.Skip).Take(page.Size).ToArray();
            }
            return page;
        }

        public static TEntity[] List<TEntity>(this DbContext db) where TEntity : DbEntity
        => db.Queryable<TEntity>().ToArray();

        public static TEntity[] List<TEntity>(this DbContext db, Expression<Func<TEntity, bool>> predicate, IOrderedQueryable<TEntity> order = null) where TEntity : DbEntity
        {
            if (order != null)
                return order.Where(predicate).ToArray();
            return db.Queryable<TEntity>().Where(predicate).ToArray();
        }

        public static TEntity Query<TEntity>(this DbContext db, Expression<Func<TEntity, bool>> predicate) where TEntity : DbEntity
        {
            try
            {
                if (predicate.IsEmpty())
                    return null;
                return db.Queryable<TEntity>().First(predicate);
            }
            catch
            {
                return null;
            }
        }

        public static TEntity Query<TEntity>(this DbContext db, string id) where TEntity : DbEntity
        {
            if (id.IsEmpty())
                return null;
            return db.Query<TEntity>(m => m.Id == id);
        }

        public static TEntity Query<TEntity>(this DbContext db, TEntity item) where TEntity : DbEntity
        {
            if (item.Id.IsEmpty())
                return null;
            return db.Query<TEntity>(m => m.Id == item.Id);
        }

        public static bool Exist<TEntity>(this DbContext db, TEntity item, bool setId = false) where TEntity : DbEntity
        {
            if (item.Id.IsEmpty())
            {
                if (setId)
                    item.Id = StringUtil.NewId();//给save->add用的
                return false;
            }
            return db.Query<TEntity>(item.Id) != null;
        }

        public static TEntity Save<TEntity>(this DbContext db, TEntity item) where TEntity : DbEntity
        {
            EntityEntry<TEntity> entry;
            if (db.Exist(item, true))
            //entry = db.Update(item);//全量更新每个字段都赋值,不好用
            {
                entry = db.Entry(item);
                entry.State = EntityState.Modified;
                foreach (var property in entry.Properties)
                    if (property.OriginalValue == null)
                        property.IsModified = false;
            }
            else
                entry = db.Add(item);
            db.SaveChanges();
            entry.State = EntityState.Detached;
            return db.Query(item);
        }

        public static TEntity[] Save<TEntity>(this DbContext db, TEntity[] items) where TEntity : DbEntity
        {
            foreach (var item in items)
                db.Save(item);
            return items;
        }

        public static bool Delete<TEntity>(this DbContext db, TEntity item) where TEntity : DbEntity
        {
            var entry = db.Remove(item);
            db.SaveChanges();
            var result = entry.State == EntityState.Deleted;
            entry.State = EntityState.Detached;
            return result;
        }

        public static int Delete<TEntity>(this DbContext db, TEntity[] items) where TEntity : DbEntity
        {
            var result = 0;
            foreach (var item in items)
                result += db.Delete(item) ? 1 : 0;
            return result;
        }

        public static void Migrate(this DbContext db)
        => db.Database.Migrate();

        public static string ProviderName(this DbContext db)
         => db.Database.ProviderName;

        public static string Type(this DbContext db)
        => db.ProviderName();

        public static DbConnection Connection(this DbContext db)
        => db.Database.GetDbConnection();

        public static string Name(this DbContext db)
        => db.Connection().Database;

        public static int ExcuteSql(this DbContext db, string sql)
        => db.Database.ExecuteSqlRaw(sql);

        //public static int ExcuteSql(this DbContext db, string sql)
        //{
        //    var result = 0;
        //    var subSqls = sql.RemoveWrappings().Split(';');
        //    foreach (var subSql in subSqls)
        //        if (subSql.IsNotEmpty())
        //            result += db.Database.ExecuteSqlRaw(subSql);
        //    return result;
        //}

        public static bool SupportComment(this DbContext db)
        {
            switch (db.Type())
            {
                case "MySql":
                case "MySql.EntityFrameworkCore":
                    return true;
            }
            return false;
        }

        public static string GetCommentSql(string dbType, string tableName, string tableComment)
        {
            switch (dbType)
            {
                case "MySql":
                case "MySql.EntityFrameworkCore":
                    //ALTER TABLE `Users` COMMENT = '啊啊啊啊啊啊啊啊啊啊啊啊';
                    return "ALTER TABLE `{}` COMMENT = '{}';".Format(tableName, tableComment);
            }
           (dbType + "数据库添加注释SQL返回默认值").Print();
            //COMMENT ON TABLE "test_table" IS '测试-测试用表1';
            return "COMMENT ON TABLE \"{}\" IS '{}';".Format(tableName, tableComment);
        }

        private static string GetMySqlColunmType(DbConnection connection, string tableName, string columnName)
        {
            var command = connection.CreateCommand();
            command.CommandText = string.Format("SELECT `COLUMN_TYPE`,`IS_NULLABLE` FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA`='{0}' AND `TABLE_NAME`='{1}' AND `COLUMN_NAME`='{2}';", connection.Database, tableName, columnName);
            //command.CommandText.Print();
            connection.Open();
            //var scalar = command.ExecuteScalar();
            var reader = command.ExecuteReader();
            if (!reader.Read())
                throw new Exception(tableName + "." + columnName + "还未生成");
            //一定要注意布尔表达式的优先级,这里的括号不能删
            var colunmType = reader.GetString("COLUMN_TYPE") + " " + (reader.GetString("IS_NULLABLE") == "YES" ? "NULL" : "NOT NULL");
            reader.Close();
            connection.Close();
            //return scalar.ToString();
            return colunmType;
        }

        public static string GetCommentSql(DbContext db, string tableName, string columnName, string columnComment)
        {
            switch (db.ProviderName())
            {
                case "MySql":
                case "MySql.EntityFrameworkCore":
                    //ALTER TABLE `Users` CHANGE COLUMN `Account` COMMENT '我1';
                    var columnType = GetMySqlColunmType(db.Connection(), tableName, columnName);
                    return "ALTER TABLE `{}` MODIFY COLUMN `{}` {} COMMENT '{}';".Format(tableName, columnName, columnType, columnComment);
                    //UPDATE `information_schema`.`COLUMNS` SET `COLUMN_COMMENT`='{}' WHERE `TABLE_SCHEMA`='manage-system' AND `TABLE_NAME`='WeatherForecasts' AND `COLUMN_NAME`='Id';
                    //MySql.Data.MySqlClient.MySqlException:“Access denied for user 'root'@'%' to database 'information_schema'”
                    //return "UPDATE `information_schema`.`COLUMNS` SET `COLUMN_COMMENT`='{}' WHERE `TABLE_SCHEMA`='{}' AND `TABLE_NAME`='{}' AND `COLUMN_NAME`='{}';".Format(columnComment, db.Name(), tableName, columnName);
            }
           (db.ProviderName() + "数据库添加注释SQL返回默认值").Print();
            //COMMENT ON COLUMN "test_table"."valid" IS '布尔测试11';
            return "COMMENT ON COLUMN \"{}\".\"{}\" IS '{}';".Format(tableName, columnName, columnComment);
        }

        public static void AddComments(this ModelSnapshot model)
        {
            model.Model.ToDebugString(MetadataDebugStringOptions.LongDefault, 0).Print();
        }

        public static int AddComments(this DbContext db)
        {
            var tableFields = db.GetFields(typeof(DbSet<>));
            var sql = new StringBuilder();
            foreach (var tableField in tableFields)
            {
                var type = tableField.PropertyType;
                type = type.GetGenericArguments()[0];
                var comment = type.GetAttribute<CommentAttribute>();
                if (comment != null)
                    sql.AppendLine(GetCommentSql(db.ProviderName(), tableField.Name, comment.Comment));
                var schema = type.GetAttribute<SwaggerSchemaAttribute>();
                if (schema != null)
                    sql.AppendLine(GetCommentSql(db.ProviderName(), tableField.Name, schema.Description));
                var columnFields = type.GetProperties();
                foreach (var columnField in columnFields)
                {
                    if (columnField.PropertyType.Name.Contains("List") || !columnField.CanWrite)
                        continue;
                    //if (columnField.Name == "LengthCM")
                    //    ;
                    comment = columnField.GetAttribute<CommentAttribute>(false);
                    if (comment != null)
                        sql.AppendLine(GetCommentSql(db, tableField.Name, columnField.Name, comment.Comment));
                    schema = columnField.GetAttribute<SwaggerSchemaAttribute>(false);
                    if (schema != null)
                        sql.AppendLine(GetCommentSql(db, tableField.Name, columnField.Name, schema.Description));
                }
            }
            if (sql.Length == 0)
                return 0;
            sql.Print();
            return db.ExcuteSql(sql.ToString());
        }
    }
}
